
USE [arirang_klist]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ts_SearchHoaDonXuatNB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ts_SearchHoaDonXuatNB]
GO
/****** Object:  StoredProcedure [dbo].[ts_SearchHoaDonXuatNB]    Script Date: 04/05/2013 17:42:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ts_SearchHoaDonXuatNB]
 @dFrom SMALLDATETIME, 
 @dTo SMALLDATETIME, 
 @cCustomer VARCHAR(33),
 @cSo_phieu VARCHAR(33),
 @cMa_dvcs VARCHAR(33)
AS
BEGIN
 SELECT a.* INTO #phtmp
  FROM tbl_acc_invoice a
  WHERE (a.invoice_date BETWEEN @dFrom AND @dTo) AND a.status <> '*' AND a.agency_id = @cMa_dvcs AND a.invoice_number LIKE  '%' + RTRIM(@cSo_phieu) + '%' 
  ORDER BY a.invoice_date, a.stt_rec

 IF @cCustomer <> '' 
  DELETE #phtmp WHERE RTRIM(agency_id) <> RTRIM(@cCustomer)
   
 SELECT a.*, b.name AS ten_hh INTO #cttmp
  FROM tbl_acc_invoice_details a 
   LEFT OUTER JOIN tbl_product b ON a.product_id = b.id
  WHERE a.stt_rec IN (SELECT stt_rec FROM #phtmp)
 
 SELECT * FROM #phtmp ORDER BY invoice_date, stt_rec
 SELECT * FROM #cttmp ORDER BY stt_rec, stt_rec0
 
 
END

GO


